﻿Imports System.Data.SqlClient

Public Class cEmployee

    Dim con As New SqlConnection(strcon)

    Public Function select_Employee() As DataTable
        Dim dt As New DataTable
        Dim oMain As New cMain
        dt = oMain.getData("SELECT EmployeeID,EmployeeName,Gender,DOB,[Address],Tel,Email,Position FROM tblEmployee WHERE Active=1")
        Return dt
    End Function

    Public Function select_Employee_byID(ByVal EmployeeID As Integer) As DataTable
        Dim dt As New DataTable
        Dim oMain As New cMain
        dt = oMain.getData("SELECT EmployeeID,EmployeeName,Gender,DOB,[Address],Tel,Email,Photo,Position FROM tblEmployee WHERE EmployeeID=N'" & EmployeeID & "'")
        Return dt
    End Function

    Public Function select_Employee_byKeyword(ByVal Keyword As String) As DataTable
        Dim dt As New DataTable
        Dim oMain As New cMain
        dt = oMain.getData("SELECT EmployeeID,EmployeeName,Gender,DOB,[Address],Tel,Email,Position FROM tblEmployee WHERE Active=1 AND LOWER(EmployeeName + Gender + [Address] + Tel + Position) LIKE N'%' + N'" & LCase(Keyword) & "' + N'%'")
        Return dt
    End Function

    Public Function insert_Employee(ByVal EmployeeName As String, ByVal Gender As String, ByVal DOB As Date, ByVal Address As String, ByVal Tel As String, ByVal Email As String, ByVal Photo As Byte(), ByVal Position As String) As Boolean
        Dim rv As Boolean = True
        Dim cmd As New SqlCommand
        cmd.CommandText = "INSERT INTO tblEmployee(EmployeeName,Gender,DOB,[Address],Tel,Email,Photo,Position) VALUES(@EmployeeName,@Gender,@DOB,@Address,@Tel,@Email,@Photo,@Position)"
        With cmd.Parameters
            .AddWithValue("@EmployeeName", EmployeeName)
            .AddWithValue("@Gender", Gender)
            .AddWithValue("@DOB", DOB)
            .AddWithValue("@Address", Address)
            .AddWithValue("@Tel", Tel)
            .AddWithValue("@Email", Email)
            If Photo IsNot Nothing Then
                .AddWithValue("@Photo", Photo)
            Else
                .Add("@Photo", SqlDbType.Image).Value = DBNull.Value
            End If
            .AddWithValue("@Position", Position)
        End With
        cmd.Connection = con
        Try
            con.Open()
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            rv = False
        Finally
            con.Close()
            cmd.Dispose()
        End Try
        Return rv
    End Function

    Public Function update_Employee(ByVal EmployeeID As Integer, ByVal EmployeeName As String, ByVal Gender As String, ByVal DOB As Date, ByVal Address As String, ByVal Tel As String, ByVal Email As String, ByVal Photo As Byte(), ByVal Position As String) As Boolean
        Dim rv As Boolean = True
        Dim cmd As New SqlCommand
        cmd.CommandText = "UPDATE tblEmployee SET EmployeeName=@EmployeeName,Gender=@Gender,DOB=@DOB,[Address]=@Address,Tel=@Tel,Email=@Email,Photo=@Photo,Position=@Position WHERE EmployeeID=@EmployeeID"
        With cmd.Parameters
            .AddWithValue("@EmployeeID", EmployeeID)
            .AddWithValue("@EmployeeName", EmployeeName)
            .AddWithValue("@Gender", Gender)
            .AddWithValue("@DOB", DOB)
            .AddWithValue("@Address", Address)
            .AddWithValue("@Tel", Tel)
            .AddWithValue("@Email", Email)
            If Photo IsNot Nothing Then
                .AddWithValue("@Photo", Photo)
            Else
                .Add("@Photo", SqlDbType.Image).Value = DBNull.Value
            End If
            .AddWithValue("@Position", Position)
        End With
        cmd.Connection = con
        Try
            con.Open()
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            rv = False
        Finally
            con.Close()
            cmd.Dispose()
        End Try
        Return rv
    End Function

    Public Function delete_Employee(ByVal EmployeeID As Integer) As Boolean
        Dim rv As Boolean = True
        Dim cmd As New SqlCommand
        cmd.CommandText = "UPDATE tblEmployee SET Active=0 WHERE EmployeeID=@EmployeeID"
        With cmd.Parameters
            .AddWithValue("@EmployeeID", EmployeeID)
        End With
        cmd.Connection = con
        Try
            con.Open()
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            rv = False
        Finally
            con.Close()
            cmd.Dispose()
        End Try
        Return rv
    End Function

End Class
